<?php

class ReportEvent extends Action {

    public function getMembersTableData() {
        $members = M("Members")->field('id,username,real_name')->select();
        $db_m_members_pdoc = M('MMembersPdoc');

        for ($i = 0; $i < count($members); $i++) {
            //$members[$i]['create_date'] = $db_m_members_pdoc->where( array('uid'=>$members[$i]['id']) )->min('create_date');
            $members[$i]['total_time_study'] = $db_m_members_pdoc->where(array('uid' => $members[$i]['id']))->sum('time_study');
        }
        return $members;
    }

    public function getMembersTableData_bak() {
        $members = D("Members")->field('uid,username')->select();
        $db_m_members_pdoc = M('MMembersPdoc');

        for ($i = 0; $i < count($members); $i++) {
//            $members[$i]['create_date'] = $db_m_members_pdoc->where( array('uid'=>$members[$i]['uid']) )->min('create_date');
            $members[$i]['total_time_study'] = $db_m_members_pdoc->where(array('uid' => $members[$i]['uid']))->sum('time_study');
        }
        return $members;
    }

    public function getMembersByTime($t_begin, $t_stop) {

        $db_m_members_pdoc = M('MMembersPdoc');
        $db_members = M("Members");

        $map['start_time'] = array('egt', $t_begin);
        $map['stop_time'] = array('ELT', $t_stop." 99:99:99");
        
        //$members = $db_members->field('id,username,real_name')->select();
        $disIds = $db_m_members_pdoc->Distinct(true)->field('uid')->where($map)->select();
        $res = null;

        if (!empty($disIds)) {
            for ($i = 0; $i < count($disIds); $i++) {
                $uid = $disIds[$i]['uid'];
                $res[$i]['id'] = $uid;
                $member = $db_members->where(array('id' => $uid, 'is_del'=>0))->find();
                $res[$i]['username']=$member['username'];
                $res[$i]['real_name']=$member['real_name'];
                $map['uid'] = $uid;
                $res[$i]['total_time_study'] = $db_m_members_pdoc->where($map)->sum('time_study');
                
            }
        }
        
        //dump($res);
        

        return $res;
        
    }

    public function getMembersByTime_bak($t_date) {

        $db_m_members_pdoc = M('MMembersPdoc');
        $db_members = D("Members");
        $map['create_date'] = array('egt', $t_date);

        $disIds = $db_m_members_pdoc->Distinct(true)->field('uid')->where($map)->select();
        $res = null;

        if (!empty($disIds)) {

            for ($i = 0; $i < count($disIds); $i++) {
                $uid = $disIds[$i]['uid'];
                $res[$i]['uid'] = $uid;
                $res[$i]['username'] = $db_members->where(array('uid' => $uid))->getField("username");

                $where['uid'] = $uid;
                $where['create_date'] = array('egt', $t_date);
                $res[$i]['create_date'] = $db_m_members_pdoc->where($where)->order('create_date')->getField('create_date');
                $res[$i]['total_time_study'] = $db_m_members_pdoc->where($where)->sum('time_study');
            }
        }

        return $res;
    }
    
    
    public function getMembersDetailTableData($t_begin, $t_stop){
        
        $db_m_members_pdoc = M('MMembersPdoc');
        $db_members = D("Members");
        $pdocs = M('Pdoc')->where(array("pdoc_isdel"=>0))->select();
        
        $res = null;
        if(!empty($t_begin)){
            $map['start_time'] = array('egt', $t_begin);
            $map['stop_time'] = array('ELT', $t_stop." 99:99:99");
            $disIds = $db_m_members_pdoc->Distinct(true)->field('uid')->where($map)->select();
        }else{
            $disIds = $db_m_members_pdoc->Distinct(true)->field('uid')->select();
        }
        
        
        if (!empty($disIds)) {
            
            $j = 0;
            for($i=0; $i<count($disIds); $i++){

                $uid = $disIds[$i]['uid'];
                $member = $db_members->where('id='.$uid)->find();
                
                
                for($j = 0; $j<count($pdocs); $j++){
                    
                    $res[$i][$j]['username'] = $member['username'];
                    $res[$i][$j]['real_name'] = $member['real_name'];
                    $res[$i][$j]['pdoc_sn'] = $pdocs[$j]['pdoc_sn'];
                    $res[$i][$j]['pdoc_catagory'] = $pdocs[$j]['pdoc_catagory'];
                    $res[$i][$j]['pdoc_name'] = $pdocs[$j]['pdoc_name'];
                    $map['uid'] = $uid;
                    $map['pdoc_id'] = $pdocs[$j]['pdoc_id'];
                    if(!empty($t_begin)){
                        $map['start_time'] = array('egt', $t_begin);
                        $map['stop_time'] = array('ELT', $t_stop." 99:99:99");
                    }
                    $res[$i][$j]['total_time_study'] = $db_m_members_pdoc->where($map)->sum('time_study');
                    
                }
                
            }
            
        }
        
        
        //($res);
        return $res;

        
    }

    public function exportTotalExcel($t_begin, $t_stop) {
        //在thinkphp中调用这个插件
        vendor("PHPExcel.PHPExcel");
        $objPHPExcel = new PHPExcel();

        //设置文档基本属性 
        $objPHPExcel->getProperties()
                ->setCreator("ctos")
                ->setLastModifiedBy("ctos")
                ->setTitle("Office 2007 XLSX Test Document")
                ->setSubject("Office 2007 XLSX Test Document")
                ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
                ->setKeywords("office 2007 openxml php")
                ->setCategory("Test result file")
        ;

        // 生成第一张excel的sheet表,一般就一张
        $objPHPExcel->setActiveSheetIndex(0);
        // 设置sheet表名字
        $objPHPExcel->getActiveSheet()->setTitle('学习汇总');

        //设置Excel每列的宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(50);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(25);

        //设置文字水平居中
        $objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


        //特定风格
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('C1')->getFont()->setBold(true);

        //设置head单元格的值
        $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A1', '员工编号')
                ->setCellValue('B1', '员工姓名')
                ->setCellValue('C1', '总共学习时间')
        ;

        if(!empty($t_begin)){
            $members = $this->getMembersByTime($t_begin, $t_stop);
        }else{
            $members = $this->getMembersTableData();
        }
        
        for ($i = 0; $i < count($members); $i++) {

            $total_time_study = $this->formatTimeStudy($members[$i]['total_time_study']);
            $objPHPExcel->getActiveSheet(0)->setCellValue('A' . ($i + 2), $members[$i]['username']);
            $objPHPExcel->getActiveSheet(0)->setCellValue('B' . ($i + 2), $members[$i]['real_name']);
            $objPHPExcel->getActiveSheet(0)->setCellValue('C' . ($i + 2), $total_time_study);
            $objPHPExcel->getActiveSheet(0)->getRowDimension($i + 2)->setRowHeight(16);
        }

        ob_clean();
        // Redirect output to a client’s web browser (Excel5)
        header('Content-Type: application/vnd.ms-excel');
        if(empty($t_begin)){
            header('Content-Disposition: attachment;filename="明细表(' . date('Ymd-His') . ').xls"');
        }else{
            header('Content-Disposition: attachment;filename="明细表(' .$t_begin.'~'.$t_stop . ').xls"');
        }
        header('Cache-Control: max-age=0');

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
    }
    
    public function exportDetailExcel($t_begin, $t_stop) {
        //在thinkphp中调用这个插件
        vendor("PHPExcel.PHPExcel");
        $objPHPExcel = new PHPExcel();

        //设置文档基本属性 
        $objPHPExcel->getProperties()
                ->setCreator("ctos")
                ->setLastModifiedBy("ctos")
                ->setTitle("Office 2007 XLSX Test Document")
                ->setSubject("Office 2007 XLSX Test Document")
                ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
                ->setKeywords("office 2007 openxml php")
                ->setCategory("Test result file")
        ;

        // 生成第一张excel的sheet表,一般就一张
        $objPHPExcel->setActiveSheetIndex(0);
        // 设置sheet表名字
        $objPHPExcel->getActiveSheet()->setTitle('明细表');

        //设置Excel每列的宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(25);

        //设置文字水平居中
        $objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


        //特定风格
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('C1')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('F1')->getFont()->setBold(true);

        //设置head单元格的值
        $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A1', '员工编号')
                ->setCellValue('B1', '员工姓名')
                ->setCellValue('C1', '文档编号')
                ->setCellValue('D1', '文档类型')
                ->setCellValue('E1', '文档名称')
                ->setCellValue('F1', '学习时间')
        ;
        
        $members = $this->getMembersDetailTableData($t_begin, $t_stop);
        
        $k = 2;
        for ($i = 0; $i < count($members); $i++) {
            $member = $members[$i];
            for($j=0; $j<count($member); $j++){
                $total_time_study = $this->formatTimeStudy($member[$j]['total_time_study']);
            
                $objPHPExcel->getActiveSheet(0)->setCellValue('A' . $k, $member[$j]['username']);
                $objPHPExcel->getActiveSheet(0)->setCellValue('B' . $k, $member[$j]['real_name']);
                $objPHPExcel->getActiveSheet(0)->setCellValue('C' . $k, $member[$j]['pdoc_sn']);
                $objPHPExcel->getActiveSheet(0)->setCellValue('D' . $k, $member[$j]['pdoc_catagory']);
                $objPHPExcel->getActiveSheet(0)->setCellValue('E' . $k, $member[$j]['pdoc_name']);
                $objPHPExcel->getActiveSheet(0)->setCellValue('F' . $k, $total_time_study);
                $objPHPExcel->getActiveSheet(0)->getRowDimension($k)->setRowHeight(16);
                $k++;
            }
            $k++;
        }

        ob_clean();
        // Redirect output to a client’s web browser (Excel5)
        header('Content-Type: application/vnd.ms-excel');
        if(empty($t_begin)){
            header('Content-Disposition: attachment;filename="明细表(' . date('Ymd-His') . ').xls"');
        }else{
            header('Content-Disposition: attachment;filename="明细表(' .$t_begin.'~'.$t_stop . ').xls"');
        }
        header('Cache-Control: max-age=0');

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
    }

    public function formatTimeStudy($seconds) {
        if (!empty($seconds)) {
            $hours = intval($seconds / 3600);
            $minutes = intval(($seconds - $hours * 3600) / 60);
            $sec = $seconds % 60;
            return $hours . "小时" . $minutes . "分" . $sec . "秒";
        }
    }

//    public function getMembersTableData(){
//        import('ORG.Util.Page');// 导入分页类
//        
//        
//    }
}

?>